package db

import (
	"errors"
	"fmt"
)

const (
	MtID1 = iota + 1
	MtID2
	MtID4       = 4
	MtID5       = 5
	MtId6       = 6
	MtId7       = 7
	MtID0       = 0
	PROCESS     = 3
	PROCESSSTAT = 1
	SALEWORK    = "返修工单"
	REPAIRWORK  = "维修工单"
)

// GetAllUsers 获取所有用户数据
func GetAllUsers() (users []*Users, err error) {
	sqlStr := `select uid,account,user_name,type_name,t_id from users as us inner join user_type as ut on us.t_id = ut.ut_id;`
	err = db.Select(&users, sqlStr)
	if err != nil {
		fmt.Println(err, "===================================================================")
		return nil, errors.New(fmt.Sprintf("获取所有用户数据操作错误，错误代码：%v", err))
	}
	return
}

// UserSignCheckData 登录时候获取用户信息
func UserSignCheckData(account *Users) (userData []*Users, bl error) {
	sqlStr := `select uid,account,user_name,t_id from users where account=? and passwd=?; `
	if bl = db.Select(&userData, sqlStr, account.Account, account.Passwd); bl != nil {
		fmt.Println("-------------------------------- 查询用户数据库错误 -------------------------------", bl)
		return
	}
	return
}

// GetAllUserType 获取所有用户类型
func GetAllUserType() (typeName []*UserType, err error) {
	sqlStr := `select ut_id, type_name from user_type;`
	if err = db.Select(&typeName, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("根据用户类型 Id 获取类型名称错误，错误代码：%v", err))
	}
	return
}

// InsertUserDataToSql 新增用户插入数据操作
func InsertUserDataToSql(user *Users) error {
	sqlStr := `Insert Into users(account,user_name,passwd,t_id) Values(?,?,?,?);`
	if _, err := db.Exec(sqlStr, user.Account, user.UserName, user.Passwd, user.TId); err != nil {
		return errors.New(fmt.Sprintf("插入用户数据到数据库错误，请联系系统管理员，错误代码：%v", err))
	}
	return nil
}

// ChangeUserData 修改更新用户数据操作
func ChangeUserData(user *Users) error {
	sqlStr := ``
	if user.Passwd != "" {
		sqlStr = `update users set account=?,user_name=?,passwd=?,t_id=? where uid=?;`
		if _, err := db.Exec(sqlStr, user.Account, user.UserName, user.Passwd, user.TId, user.Uid); err != nil {
			return errors.New(fmt.Sprintf("更新用户数据到数据库出错，错误代码：%v", err))
		}
	} else {
		sqlStr = `update users set account=?,user_name=?,t_id=? where uid=?;`
		if _, err := db.Exec(sqlStr, user.Account, user.UserName, user.TId, user.Uid); err != nil {
			return errors.New(fmt.Sprintf("更新用户数据到数据库出错，错误代码：%v", err))
		}
	}
	return nil
}

// DelUserData 删除用户操作
func DelUserData(user *Users) error {
	sqlStr := `Delete From users where uid=?;`
	if _, err := db.Exec(sqlStr, user.Uid); err != nil {
		return errors.New(fmt.Sprintf("删除用户失败，错误代码:%v", err))
	}
	return nil
}

// GetAllClients 获取所有客户信息
func GetAllClients() (clients []*Client, err error) {
	sqlStr := `SELECT 
						cid,
						client_name,
						phone,
						addr
				From 
					clientAddr 
				AS 
					ca
				Right Join 
					client As c
				ON 
					ca.client_id=c.cid
				ORDER BY  ca.client_id Desc;`
	if err = db.Select(&clients, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("获取所有客户信息失败，错误代码：%v", err))
	}
	return
}

// GetSalePeople 获取所有销售/业务人员信息
func GetSalePeople() (users []*Users, err error) {
	sqlStr := `select uid,user_name from users where t_id=? or t_id=?;`
	if err = db.Select(&users, sqlStr, 4, 5); err != nil {
		return nil, errors.New(fmt.Sprintf("获取销售人员信息错误，请联系系统管理员解决，错误代码：%v", err))
	}
	return
}

// PhoneGetClient 根据客户注册的手机号查询客户信息
func PhoneGetClient(phone string) (ret string, err error) {
	sqlStr := `Select phone From client Where phone=?`
	if err := db.Get(&ret, sqlStr, phone); err != nil {
		return "", errors.New(fmt.Sprintf("添加信息失败，给您带来不便敬请谅解，错误代码：%v", err))
	}
	return
}

// InsertClientDatToSql 插入客户数据到数据库  todo 地址已于客户表分开，应重新书写插入地址到地址表中
func InsertClientDatToSql(client *Client) (err error) {
	sqlStr := `Insert Into client(client_name,phone) Values(?,?);`
	if _, err = db.Exec(sqlStr, client.ClientName, client.Phone); err != nil {
		return errors.New(fmt.Sprintf("添加客户失败，错误代码：%v", err))
	}
	return
}

// AddClientInsertClientAddrToSql 在后台手动添加客户数据时插入客户地址到客户地址表
func AddClientInsertClientAddrToSql(client *Client) (err error) {
	sqlStr := `Insert Into clientAddr(addr, client_id) Select ?,cid From client Where client_name=? And phone=?;`
	if _, err = db.Exec(sqlStr, client.Addr, client.ClientName, client.Phone); err != nil {
		return errors.New(fmt.Sprintf("添加客户数据是添加地址信息失败，错误代码：%v", err))
	}
	return nil
}

// GetClientAddr 后台修改客户地址时先查询该用户地址
func GetClientAddr(client *Client) (addr string, err error) {
	sqlStr := `Select addr From clientAddr Where client_id=?;`
	if err = db.Get(&addr, sqlStr, client.Cid); err != nil {
		return "", nil
	}
	return
}

// AddClientUpdateClientAddrToSql 在后台修改客户数据时插入客户地址到客户地址表
func AddClientUpdateClientAddrToSql(client *Client) (err error) {
	sqlStr := `Update clientAddr set addr=? Where client_id=?;`
	if _, err = db.Exec(sqlStr, client.Addr, client.Cid); err != nil {
		return errors.New(fmt.Sprintf("添加客户数据是添加地址信息失败，错误代码：%v", err))
	}
	return nil
}

// UpdateClientDat 更新客户数据信息 todo 地址已于客户表分开，应重新书写插入地址到地址表中
func UpdateClientDat(client *Client) error {
	sqlStr := `update client set client_name=?,phone=? where cid=?;`
	if _, err := db.Exec(sqlStr, client.ClientName, client.Phone, client.Cid); err != nil {
		return errors.New(fmt.Sprintf("更新客户信息失败，错误代码:%v", err))
	}
	return nil
}

// DelClientData 删除客户数据
func DelClientData(cli *Client) error {
	sqlStr := `Delete From client Where cid=?;`
	if _, err := db.Exec(sqlStr, cli.Cid); err != nil {
		return errors.New(fmt.Sprintf("删除客户信息失败，错误代码：%v", err))
	}
	return nil
}

// GetLogTicsDat 获取所有物流信息
func GetLogTicsDat() (logs []*Logistics, err error) {
	sqlStr := `Select lid,lname,butt,phone From logistics order by lid Desc;`
	if err = db.Select(&logs, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("获取物流数据失败，错误代码：%v", err))
	}
	return
}

// AddLogTicToSql 添加物流数据
func AddLogTicToSql(log *Logistics) error {
	sqlStr := `Insert Into logistics(lname,butt,phone) Values(?,?,?);`
	if _, err := db.Exec(sqlStr, log.Lname, log.Butt, log.Phone); err != nil {
		return errors.New(fmt.Sprintf("添加物流信息失败，错误代码：%v", err))
	}
	return nil
}

// UpLodTicData 更新物流信息
func UpLodTicData(mod *Model) error {
	sqlStr := `update logistics set lname=?,butt=?,phone=? where lid=?;`
	if _, err := db.Exec(sqlStr, mod.Logistics.Lname, mod.Logistics.Butt, mod.Logistics.Phone, mod.Logistics.Lid); err != nil {
		return errors.New(fmt.Sprintf("更新物流信息失败，错误代码：%v", err))
	}
	return nil
}

// DelLogTicData 删除物流信息
func DelLogTicData(mod *Model) error {
	sqlStr := `delete from logistics where lid=?;`
	if _, err := db.Exec(sqlStr, mod.Logistics.Lid); err != nil {
		return errors.New(fmt.Sprintf("删除物流信息失败，错误代码：%v", err))
	}
	return nil
}

// GetAllBomName 获取所有 bom 名称
func GetAllBomName() (bomNames []*BomNameTab, err error) {
	sqlStr := `Select bn_id, b_name From bom_name order by bn_id Desc;`
	if err = db.Select(&bomNames, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("获取数据失败，错误代码：%v", err))
	}
	return
}

// AddBomNameDat 添加 bom 名称
func AddBomNameDat(mod *Model) error {
	sqlStr := `Insert Into bom_name(b_name) Values(?);`
	if _, err := db.Exec(sqlStr, mod.BomNameTab.BName); err != nil {
		return errors.New(fmt.Sprintf("添加bom名称数据失败，错误代码：%v", err))
	}
	return nil
}

// UpdateBomName 修改 bom 名称
func UpdateBomName(mod *Model) error {
	sqlStr := `Update bom_name set b_name=? where bn_id=?;`
	if _, err := db.Exec(sqlStr, mod.BomNameTab.BName, mod.BomNameTab.BnId); err != nil {
		return errors.New(fmt.Sprintf("更新数据失败，错误代码：%v", err))
	}
	return nil
}

// DeleteBomName 删除
func DeleteBomName(mod *Model) error {
	sqlStr := `delete from bom_name where bn_id=?;`
	if _, err := db.Exec(sqlStr, mod.BomNameTab.BnId); err != nil {
		return errors.New(fmt.Sprintf("删除信息失败，错误代码：%v", err))
	}
	return nil
}

// GetAllMater 获取所有物料数据方法
func GetAllMater() (maters []*Mater, err error) {
	sqlStr := `Select mid,mname,brand,model,mater_type_name From mater Inner Join mater_type as mt on mater.mater_type_id = mt.mt_id  Order by mid Desc;`
	if err = db.Select(&maters, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("获取物料数据失败，错误代码：%v", err))
	}
	return
}

// GetALlMaterType 获取所有物料类型方法
func GetALlMaterType() (materTypes []*MaterType, err error) {
	sqlStr := `Select mt_id,mater_type_name From mater_type;`
	if err = db.Select(&materTypes, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("获取物料类型数据错误，错误代码：%v", err))
	}
	return
}

// AddMaterDat 添加物料数据库操作方法
func AddMaterDat(model *Model) error {
	sqlStr := `Insert Into mater(mname,brand,model,mater_type_id) Values(?,?,?,?);`
	if _, err := db.Exec(sqlStr, model.Mater.Mname, model.Mater.Brand, model.Mater.Model, model.Mater.MTypeId); err != nil {
		return errors.New(fmt.Sprintf("添加物料数据失败，错误代码：%v", err))
	}
	return nil
}

// UpdateMater 修改物料信息
func UpdateMater(model *Model) error {
	sqlStr := `update mater set mname=?,brand=?,model=?,mater_type_id=? where mid=?;`
	if _, err := db.Exec(sqlStr, model.Mater.Mname, model.Mater.Brand, model.Mater.Model, model.Mater.MTypeId, model.Mater.Mid); err != nil {
		return errors.New(fmt.Sprintf("更新数据失败，错误代码：%v", err))
	}
	return nil
}

// DelMaterDat 删除物料数据
func DelMaterDat(model *Model) error {
	sqlStr := `Delete From mater Where mid=?;`
	if _, err := db.Exec(sqlStr, model.Mater.Mid); err != nil {
		return errors.New(fmt.Sprintf("删除信息失败，错误代码：%v", err))
	}
	return nil
}

// PrinterSqlFunc 打印机数据操作数据库函数
func PrinterSqlFunc(option string, model *Model) (dats []*Printer, err error) {
	if option == "" {
		return nil, errors.New(fmt.Sprintf("操作失败，d对数据库的操作目的不明显.给您带来不便敬请谅解~~!"))
	}
	sqlStr := ``
	switch option {
	case "get":
		sqlStr = `Select pid,p_name,p_net_addr,bt_ser_addr From printer Order By pid Desc;`
		if err = db.Select(&dats, sqlStr); err != nil {
			return nil, errors.New(fmt.Sprintf("获取打印机数据信息失败，错误代码：%v", err))
		}
		//return
	case "insert":
		sqlStr = `Insert Into printer(p_name,p_net_addr,bt_ser_addr) Values(?,?,?);`
		if _, err = db.Exec(sqlStr, model.Printer.Pname, model.Printer.PNetAddr, model.Printer.BTSerAddr); err != nil {
			return nil, errors.New(fmt.Sprintf("添加打印机数据信息失败，错误代码：%v", err))
		}
	case "update":
		sqlStr = `Update printer set p_name=?,p_net_addr=?,bt_ser_addr=? where pid=?;`
		if _, err = db.Exec(sqlStr, model.Printer.Pname, model.Printer.PNetAddr, model.Printer.BTSerAddr, model.Printer.Pid); err != nil {
			return nil, errors.New(fmt.Sprintf("更新打印机数据信息失败，错误代码：%v", err))
		}
	case "del":
		sqlStr = `Delete From printer where pid=?;`
		if _, err = db.Exec(sqlStr, model.Printer.Pid); err != nil {
			return nil, errors.New(fmt.Sprintf("删除打印机数据信息失败，错误代码：%v", err))
		}
	default:
		return nil, errors.New(fmt.Sprintf("数据库操作非法，给您带来不便敬请谅解~~!"))
	}
	return
}

// GetAllMaterIdName 获取所有物料为成品或半成品的物料，并且只需要Id 和物料名称
func GetAllMaterIdName() (mates []*Mater, err error) {
	sqlStr := `Select mid,mname From mater Where mater_type_id = ? Or mater_type_id = ?;`
	if err = db.Select(&mates, sqlStr, 1, 2); err != nil {
		return nil, errors.New(fmt.Sprintf("获取成品数据失败，错误代码：%v", err))
	}
	return
}

// GetMaterModel 产品组装根据物料名称获取物料型号
func GetMaterModel(mid int) (model string, err error) {
	sqlStr := `select model from mater where mid = ?;`
	if err = db.Get(&model, sqlStr, mid); err != nil {
		return "", errors.New(fmt.Sprintf("获取物料型号失败，错误代码：%v", err))
	}
	return
}

// GetMaterNotOneAndTwo 产品组装获取不包含成品的物料
func GetMaterNotOneAndTwo() (mates []*Mater, err error) {
	sqlStr := `Select mid,mname,model,mater_type_name From mater As m Inner Join mater_type As mt on m.mater_type_id = mt.mt_id  Where mater_type_id Not In (?);`
	if err = db.Select(&mates, sqlStr, MtID1); err != nil {
		return nil, errors.New(fmt.Sprintf("获取所有子件信息失败，错误代码：%v", err))
	}
	return
}

/* ===================================================== bom 数据操作 ==================================================
=============== 插入操作 */

// InsertDatToMaterBom 插入数据到 物料Bom 表
func InsertDatToMaterBom(model *Model) (err error) {
	sqlStr := `Insert Into mater_bom(m_id,fm_id,mater_lv,dosage,bom_name_id) Values(?,?,?,?,?);`
	if _, err = db.Exec(sqlStr, model.MaterBom.MId, model.MaterBom.FmId, model.MaterBom.MaterLv, model.MaterBom.Dosage, model.MaterBom.BomNameId); err != nil {
		return errors.New(fmt.Sprintf("添加数据失败，错误代码：%v", err))
	}
	return nil
}

// GetProductBomMbId 查询成品在 Bom 表中的编号Id
func GetProductBomMbId(mid int) (mbId int, err error) {
	sqlStr := `Select mbId From mater_bom Where m_id = ?;`
	if err = db.Get(&mbId, sqlStr, mid); err != nil {
		return 0, errors.New(fmt.Sprintf("根据成品Id获取成品在bom表中的编号失败，错误代码：%v", err))
	}
	return
}

// GetChildType 根据子件Id 查询 mater 表中的子件类型如果类型为2则在查询 bom 表中的所有子件
func GetChildType(cid int) (mid []*MaterBom, err error) {
	sqlStr := `
		SELECT m_id,fm_id,dosage
		FROM mater_bom AS tmp4
		INNER JOIN 
			(SELECT 
				mbid,
				bom_name_id
			FROM mater_bom AS tmp1
			INNER JOIN 
				(SELECT mid,
				mater_type_id
				FROM mater
				WHERE mid = ?) AS tmp2  -- ? 要查询的子件Id
					ON tmp1.m_id = tmp2.mid
				WHERE tmp2.mater_type_id = ?) AS tmp3  -- 需要查的类型Id固定为2表示半成品
				ON tmp4.bom_name_id = tmp3.bom_name_id
				AND tmp4.fm_id = tmp3.mbid;
	`
	if err = db.Select(&mid, sqlStr, cid, MtID2); err != nil {
		return nil, errors.New(fmt.Sprintf("查询子件类型错误，错误代码：%v", err))
	}
	return
}

// ================ 查询操作

// =====================================================================================================================

// GetAllSaleAndBusiness 从 users 表中获取所有类型为销售或业务的人员
func GetAllSaleAndBusiness() (users []*Users, err error) {
	sqlStr := `select uid,user_name from users where t_id = ? or t_id = ?;`
	if err = db.Select(&users, sqlStr, MtID4, MtID5); err != nil {
		return nil, errors.New(fmt.Sprintf("获取销售人员信息失败，错误代码：%v", err))
	}
	return
}

// GetIsBomTypeMater 根据 bom 名称获取所有包含该 bom 名称的物料数据 todo 此处去掉了 mater_lv = 0 的条件，有问题需要在加上
func GetIsBomTypeMater(bId int) (mater []*Mater, err error) {
	sqlStr := `
		SELECT 
		      	mid,
				mname,
				model
		FROM mater AS m1
		INNER JOIN mater_bom AS m2
			ON m1.mid = m2.m_id
		WHERE m2.bom_name_id = ?;
	`
	if err = db.Select(&mater, sqlStr, bId); err != nil {
		return nil, errors.New(fmt.Sprintf("根据配置名称获取相关配置清单物料失败， 错误代码：%v", err))
	}
	return
}

// GetCodeBid 插入条码前先根据生成的条码获取对应条码Id
func GetCodeBid(code string) bool {
	var id int
	sqlStr := `Select bid From code Where barcode=?;`
	if err := db.Get(&id, sqlStr, code); err != nil {
		return true
	}
	return false
}

// CodeSqlOpFunc 条码数据库相关操作方法
func CodeSqlOpFunc(option string, model *Model) (codes []*Printer, err error) {
	sqlStr := ``
	switch option {
	case "insert":
		sqlStr = `Insert Into code(barcode,c_id,u_id,m_id,bom_name_id,outlib_user_id) Values(?,?,?,?,?,?);`
		if _, err = db.Exec(sqlStr, model.Code.BarCode, model.Code.CId, model.Code.UId, model.Code.MId, model.Code.BomNamId, model.Code.OutLibUserId); err != nil {
			return nil, errors.New(fmt.Sprintf("【%v】条码存入数据库错误，打印出来的该条码无效，错误代码：%v", model.Code.BarCode, err))
		}
	case "get": // 获取打印机配置信息
		sqlStr = `Select p_name,p_net_addr,bt_ser_addr From printer Where pid = ?;`
		if err = db.Select(&codes, sqlStr, model.Printer.Pid); err != nil {
			return nil, errors.New(fmt.Sprintf("打印条码失败，错误代码：%v", err))
		}
	case "update":
		sqlStr = `Update code Set outlib_user_id=?,outlib_date=?,warranty_time=?,link_order=? Where barcode=?;`
		if _, err = db.Exec(sqlStr, model.Code.OutLibUserId, model.Code.OutLibDate, model.Code.WarrantyTime, model.Code.LinkOrder, model.Code.BarCode); err != nil {
			return nil, errors.New(fmt.Sprintf("发货失败，错误代码：%v", err))
		}
	}
	return
}

// GetTypeMaterDat 根据物料类型获取物料数据方法
func GetTypeMaterDat(model *Model) (maters []*Mater, err error) {
	sqlStr := `Select mid,mname,brand,model From mater  Where mater_type_id=?;`
	if err = db.Select(&maters, sqlStr, model.MaterType.MtId); err != nil {
		return nil, errors.New(fmt.Sprintf("根据设备类型获取设备数据错误，错误代码：%v", err))
	}
	return
}

// GetProvinceDat 获取所有省份数据
func GetProvinceDat() (provinces []*Province, err error) {
	sqlStr := `Select ProId,ProName From province Order By ProID Asc;`
	if err = db.Select(&provinces, sqlStr); err != nil {
		return nil, errors.New(fmt.Sprintf("获取所有省份信息失败，错误代码：%v", err))
	}
	return
}

// GetProCityDat 根据省份ID 获取所有市级数据
func GetProCityDat(proId int) (cityArray []*City, err error) {
	sqlStr := `select CityId,CityName,ProID from city Where ProID=? Order By CityId asc;`
	if err = db.Select(&cityArray, sqlStr, proId); err != nil {
		return nil, errors.New(fmt.Sprintf("根据身份 Id 获取所属市错误，错误代码：%v", err))
	}
	return
}

// InsertUserDatToSql 插入客户数据到数据库
func InsertUserDatToSql(model *Model) error {
	sqlStr := `Insert Into client(client_name,phone) Values(?,?);`
	if _, err := db.Exec(sqlStr, model.Client.ClientName, model.Client.Phone); err != nil {
		return errors.New(fmt.Sprintf("添加资料失败，错误代码：%v", err))
	}
	return nil
}

// GetClientIsNull 根据客户登录获取客户信息是否为空
func GetClientIsNull(model *Model) (bool, error) {
	var phone string
	sqlStr := `Select phone From client where phone=?;`
	if err := db.Get(&phone, sqlStr, model.Client.Phone); err != nil {
		return false, errors.New(fmt.Sprintf("登录失败，您尚未添加个人信息哦，错误代码：%v", err))
	}
	return true, nil
}

// GetClientUserName 根据从 session 中获取到的值获取客户姓名
func GetClientUserName(model *Model) (name string, err error) {
	sqlStr := `Select client_name From client Where phone=?;`
	if err = db.Get(&name, sqlStr, model.Client.Phone); err != nil {
		return "", errors.New(fmt.Sprintf("获取联系人错误，错误代码：%v", err))
	}
	return
}

// GetUserIdDat 根据从 session 中获取到的值获取用户Id
func GetUserIdDat(model *Model) (cid int, err error) {
	sqlStr := `Select cid From client Where phone=?;`
	if err = db.Get(&cid, sqlStr, model.Client.Phone); err != nil {
		return 0, errors.New(fmt.Sprintf("获取用户编号失败，错误代码：%v", err))
	}
	return
}

// GetClientAddress 根据从 session 中获取到的值获取客户地址
func GetClientAddress(model *Model) (addr []string, err error) {
	sqlStr := `Select addr From clientAddr ca Inner Join client c ON ca.client_id = c.cid Where c.cid=? And c.phone=?;`
	if err = db.Select(&addr, sqlStr, model.Client.Cid, model.Client.Phone); err != nil {
		return nil, errors.New(fmt.Sprintf("获取地址信息失败，错误代码：%v", err))
	}
	return
}

// InsertClientAddrToSql 添加客户地址到数据库
func InsertClientAddrToSql(option string, model *Model) error {
	switch option {
	case "确认":
		sqlStr := `Insert Into clientAddr(addr,client_id) Values(?,?);`
		if _, err := db.Exec(sqlStr, model.ClientAddr.Addr, model.ClientAddr.ClientId); err != nil {
			return errors.New(fmt.Sprintf("添加地址失败，错误代码：%v", err))
		}
	case "更新":
		sqlStr := `Update clientAddr Set addr=? where client_id=?;`
		if _, err := db.Exec(sqlStr, model.ClientAddr.Addr, model.ClientAddr.ClientId); err != nil {
			return errors.New(fmt.Sprintf("更新地址失败，错误代码：%v", err))
		}
	}
	return nil
}

// GetClientName 根据客户登录使用的手机号获取客户名
func GetClientName(model *Model) (clientName string, err error) {
	sqlStr := `select client_name from client where phone=?;`
	if err = db.Get(&clientName, sqlStr, model.Client.Phone); err != nil {
		return "", errors.New(fmt.Sprintf("获取用户名称失败，错误代码：%v", err))
	}
	return
}

// GetSaleId 根据客户Id 获取关联的销售Id
func GetSaleId(model *Model) (saleId int, err error) {
	sqlStr := `Select u_id From client Where cid=?;`
	if err = db.Get(&saleId, sqlStr, model.Client.Cid); err != nil {
		return 0, errors.New(fmt.Sprintf("添加工单时根据客户Id获取关联的销售失败，错误代码：%v", err))
	}
	return
}

// InsertWorkInfoToSql 客户创建工单插入到数据库操作
func InsertWorkInfoToSql(model *Model) error {
	sqlStr := `Insert Into work(process,state,m_id,u_id,c_id,l_id,work_type,issue_describe,log_num,mater_num,create_date) Values(?,?,?,?,?,?,?,?,?,?,?);`
	if _, err := db.Exec(sqlStr, model.Work.Process, model.Work.State, model.Work.MId, model.Work.UId, model.Work.CId, model.Work.LId, model.Work.WorkType, model.Work.IssueDescribe, model.Work.LogNum, model.Work.MaterNum, model.Work.CreateDate); err != nil {
		return errors.New(fmt.Sprintf("添加工单失败，后台系统错误给您带来不便敬请谅解，错误代码：%v", err))
	}
	return nil
}

// GetWorkIdAndType 客户创建工单时插入工单数据成功后从工单表查询数据插入到待办事项表中
func GetWorkIdAndType(logNum string) (works []*Work, err error) {
	sqlStr := `Select wid,work_type,u_id,m_id From  work Where state = ? And log_num=?;`
	if err = db.Select(&works, sqlStr, 0, logNum); err != nil {
		return nil, errors.New(fmt.Sprintf("添加工单失败，后台系统错误给您带来不便敬请谅解，错误代码：%v", err))
	}
	return
}

// InsertDatToUpcoming 插入数据到待办事项表
func InsertDatToUpcoming(upcoming *UpComing) error {
	sqlStr := `Insert Into upcoming(up_name,w_id,u_id,m_id,stat_id) Values(?,?,?,?,?);`
	if _, err := db.Exec(sqlStr, upcoming.UpName, upcoming.WID, upcoming.UID, upcoming.MID, upcoming.StatId); err != nil {
		return errors.New(fmt.Sprintf("添加工单失败，后台系统错误给您带来不便敬请谅解，错误代码：%v", err))
	}
	return nil
}

// GetWorkOrderDat 客户查看与自己有关的售后工单信息
func GetWorkOrderDat(statId string, cid int) (works []*WorkTmp, err error) {
	var sqlStr string
	overStr := `
				SELECT 
				       log_num,
					   work_type,
					   mname,
					   issue_describe,
					   state,
					   create_date,
					   express_num,
					   crate_date
				FROM   return_work AS rw
					   RIGHT JOIN (SELECT wid,
										  log_num,
										  work_type,
										  mname,
										  issue_describe,
										  state,
										  create_date
								   FROM   work AS w
										  INNER JOIN mater AS m
												  ON w.m_id = m.mid
								   WHERE state=? And c_id = ? And work_type=?) AS T1
			   ON T1.wid = rw.work_id;
				`
	switch statId {
	case "0":
		// 获取状态ID 为 0 的数据
		if err = db.Select(&works, overStr, statId, cid, SALEWORK); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据失败，错误代码：%v", err))
		}
	case "1":
		// 获取状态Id 为 1 的数据
		if err = db.Select(&works, overStr, statId, cid, SALEWORK); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据失败，错误代码：%v", err))
		}
	case "2":
		// 获取状态Id 为 2 或 3 的工单数据
		sqlStr = `Select log_num,work_type,mname,issue_describe,state,create_date From work As w Inner Join mater On m_id = mid Where state=? Or state=? And c_id=? And work_type=?;`
		if err = db.Select(&works, sqlStr, statId, PROCESS, cid, SALEWORK); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据失败，错误代码：%v", err))
		}
	case "3":
		// 获取状态Id 为 4 正在返途给客户的数据 todo 快递单号与快递公司以及返回时间应使用返回的快递单号和快递公司
		if err = db.Select(&works, overStr, statId, cid, SALEWORK); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据失败，错误代码：%v", err))
		}
	case "4":
		// 获取状态Id 为 5 已完成的工单数据 todo 快递单号与快递公司以及返回时间应使用返回的快递单号和快递公司
		if err = db.Select(&works, overStr, statId, cid, SALEWORK); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据失败，错误代码：%v", err))
		}
	default:
		// 获取当前客户的所有数据
		allWorkStr := `
				SELECT 
				       log_num,
					   work_type,
					   mname,
					   issue_describe,
					   state,
					   create_date,
					   express_num,
					   crate_date
				FROM   return_work AS rw
					   RIGHT JOIN (SELECT wid,
										  log_num,
										  work_type,
										  mname,
										  issue_describe,
										  state,
										  create_date
								   FROM   work AS w
										  INNER JOIN mater AS m
												  ON w.m_id = m.mid
								   WHERE c_id = ? And work_type=?) AS T1
			   ON T1.wid = rw.work_id;
				`
		if err = db.Select(&works, allWorkStr, cid, SALEWORK); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据失败，错误代码：%v", err))
		}
	}
	return
}

// GetRepairWorkDat 客户查获取自己有关的维修工单数据
func GetRepairWorkDat(orderTpeId string, cid int) (works []*ClientGetWorkDatsTmp, err error) {
	sqlStr := `select
					pay_id,mid,mname,work_type,create_date,log_num,amount,
					Case
						When pay_stat=0 Then '待付款'
						When pay_stat=1 Then '已付款'
					END 'pay_stat',
					Case
						When state=0 Then '待收货'
						When state=1 Then '已收货'
						When state=2 Then '维修中'
						When state=3 Then '维修中'
						When state=4 Then '返回中'
						When state=5 Then '已完成'
					END 'work_state'
				From
					mater As m
				Inner Join(
					Select
						pay_id,m_id,work_type,create_date,log_num,amount,pay_stat,state
					From
						pay As p
					Inner Join (
						Select
							wid,m_id,work_type,create_date,log_num,state
						From
							work
						Where
							work_type=? And c_id=?) As w
					On p.work_id = w.wid And pay_stat=?) As pw
				On m.mid  = pw.m_id;`
	switch orderTpeId {
	case "1": // 获取未支付的数据
		if err = db.Select(&works, sqlStr, REPAIRWORK, cid, MtID0); err != nil {
			return nil, errors.New(fmt.Sprintf("哦豁，获取所有维修工单数出错了，错误代码：%v", err))
		}
	case "2": // 获取已支付的数据
		if err = db.Select(&works, sqlStr, REPAIRWORK, cid, MtID1); err != nil {
			return nil, errors.New(fmt.Sprintf("哦豁，获取所有维修工单数出错了，错误代码：%v", err))
		}
	default: // 获取所有数据
		sqlStr = `Select
					pay_id,mid,mname,work_type,create_date,log_num,amount,
					Case
						When pay_stat=0 Then '待付款'
						When pay_stat=1 Then '已付款'
					END 'pay_stat',
					Case
						When state=0 Then '待收货'
						When state=1 Then '已收货'
						When state=2 Then '维修中'
						When state=3 Then '维修中'
						When state=4 Then '返回中'
						When state=5 Then '已完成'
					END 'work_state'
				From
					mater As m
				Right Join(
					Select
						pay_id,m_id,work_type,create_date,log_num,amount,pay_stat,state
					From
						pay As p
					Right Join (
						Select
							wid,m_id,work_type,create_date,log_num,state
						From
							work
						Where
							work_type=? And c_id=?) As w
					On p.work_id = w.wid) As pw
				On m.mid  = pw.m_id;`
		if err = db.Select(&works, sqlStr, REPAIRWORK, cid); err != nil {
			return nil, errors.New(fmt.Sprintf("哦豁，获取所有维修工单数出错了，错误代码：%v", err))
		}
	}
	return
}

// GetSearchWorkDat 客户通过搜索获取工单数据
func GetSearchWorkDat(id string, cid int) (works []*WorkTmp, err error) {
	sqlStr := `
				SELECT 
				       log_num,
					   work_type,
					   mname,
					   issue_describe,
					   state,
					   create_date,
					   express_num,
					   crate_date
				FROM   return_work AS rw
					   RIGHT JOIN (SELECT wid,
										  log_num,
										  work_type,
										  mname,
										  issue_describe,
										  state,
										  create_date
								   FROM   work AS w
										  INNER JOIN mater AS m
												  ON w.m_id = m.mid
								   WHERE  c_id = ?
										  AND log_num = ?
										   OR RIGHT(log_num, 4) = ?
										   OR log_num LIKE ?) AS T1
			   ON T1.wid = rw.work_id;
			`
	if err = db.Select(&works, sqlStr, cid, id, "%"+id+"%", "%"+id+"%"); err != nil {
		return nil, errors.New(fmt.Sprintf("获取数据失败，错误代码：%v", err))
	}
	return
}

// GetAllUpcomingDat 获取待办事件数据
func GetAllUpcomingDat(workId, statId string) (upcomings []*UpcomingTmp, err error) {
	switch workId {
	case "": // 获取待办事项数据
		sqlStr := ``
		if statId == "" {
			sqlStr = `
					SELECT 
						   wid,
						   up_name,
						   client_name,
						   mname,
						   mater_num,
						   process,
						   log_num,
						   user_name
					FROM   upcoming AS up
						   INNER JOIN (SELECT wid,
											  client_name,
											  mname,
											  mater_num,
											  process,
											  log_num,
											  user_name
									   FROM   client AS c
											  INNER JOIN (SELECT 
															wid,
															 process,
															 c_id,
															 log_num,
															 mater_num,
															 mname,
															 user_name
											  FROM   users AS u
													 INNER JOIN (SELECT 
															wid,
															process,
															u_id,
															c_id,
															log_num,
															mater_num,
															mname
													 FROM   
															mater AS m
													 INNER JOIN 
															work AS w
													 ON w.m_id = m.mid) T1
											  ON 
													T1.u_id = u.uid
											  WHERE  
													u.t_id = ? OR u.t_id = ?) AS T2
						   ON c.cid = T2.c_id) AS T3
					ON up.w_id = T3.wid;  
				`
			if err = db.Select(&upcomings, sqlStr, MtID4, MtID5); err != nil {
				return nil, errors.New(fmt.Sprintf("获取待办事项数据错误，错误代码：%v", err))
			}
		} else {
			sqlStr = `
		SELECT 
     		   wid,
			   up_name,
			   client_name,
			   mname,
			   mater_num,
			   process,
			   log_num,
			   user_name
		FROM   upcoming AS up
			   INNER JOIN (SELECT wid,
								  client_name,
								  mname,
								  mater_num,
								  process,
								  log_num,
								  user_name
						   FROM   client AS c
								  INNER JOIN (SELECT 
												wid,
												 process,
												 c_id,
												 log_num,
												 mater_num,
												 mname,
												 user_name
								  FROM   users AS u
										 INNER JOIN (SELECT 
												wid,
												process,
												u_id,
												c_id,
												log_num,
												mater_num,
												mname
										 FROM   
												mater AS m
										 INNER JOIN 
												work AS w
										 ON w.m_id = m.mid) T1
								  ON 
										T1.u_id = u.uid
 								  WHERE  
										u.t_id = ? OR u.t_id = ?) AS T2
			   ON c.cid = T2.c_id) AS T3
	    ON up.w_id = T3.wid Where process = ?;  
	`
			if err = db.Select(&upcomings, sqlStr, MtID4, MtID5, statId); err != nil {
				return nil, errors.New(fmt.Sprintf("获取待办事项数据错误，错误代码：%v", err))
			}
		}
	default: // 获取待办事项详情信息
		sqlStr := `
		SELECT 
     		   wid,
			   up_name,
			   client_name,
			   mname,
			   mater_num,
		       create_date,
		       issue_describe,
			   process,
			   log_num,
			   user_name
		FROM   upcoming AS up
			   INNER JOIN (SELECT wid,
								  client_name,
								  mname,
								  mater_num,
			                      create_date,
			                      issue_describe,
								  process,
								  log_num,
								  user_name
						   FROM   client AS c
								  INNER JOIN (SELECT 
												wid,
												 process,
												 c_id,
												 log_num,
												 mater_num,
												 create_date,
												 issue_describe,
												 mname,
												 user_name
								  FROM   users AS u
										 INNER JOIN (SELECT 
												wid,
												process,
												u_id,
												c_id,
												log_num,
												mater_num,
												create_date,
												issue_describe,
												mname
										 FROM   
												mater AS m
										 INNER JOIN 
												work AS w
										 ON w.m_id = m.mid) T1
								  ON 
										T1.u_id = u.uid
 								  WHERE  
										u.t_id = ? OR u.t_id = ?) AS T2
			   ON c.cid = T2.c_id) AS T3
	    ON up.w_id = T3.wid Where wid = ?;  
	`
		if err = db.Select(&upcomings, sqlStr, MtID4, MtID5, workId); err != nil {
			return nil, errors.New(fmt.Sprintf("获取待办事项数据错误，错误代码：%v", err))
		}
	}
	return
}

// UpdateWorkProcess 审批后更改 Process 状态为 1
func UpdateWorkProcess(workId string) error {
	sqlStr := `Update work set process=? where wid=?;`
	if _, err := db.Exec(sqlStr, PROCESSSTAT, workId); err != nil {
		return errors.New(fmt.Sprintf("服务器后台错误，操作失败，错误代码：%v", err))
	}
	return nil
}

// GetBusWorkOrder 获取业务工单数据
func GetBusWorkOrder(statId string) (dats []*BusWorkOrderTmp, err error) {
	var sqlStr string = ``
	//0待收货，1已收货，2维修中，3外发中，4返途中，5已完成
	switch statId {
	case "0": // 获取 state 状态为 0  的数据
		sqlStr = `
			Select 
				wid,
				state,
				Case 
					When state=0 Then '待收货' 
					When state=1 Then '待派单' 
					When state=2 Then '内部维修中' 
					When state=3 Then '外发维修中' 
					When state=4 Then '待返客户' 
					When state=5 Then '已完成'
				END 'work_state',
				mname,
				model,
				user_name,
				client_name,
			   log_num,
				out_firm_name,
				issue_describe,
				mater_num,
				create_date,
				crate_date,
				express_num,
			    work_type,
			    o_number,
			    Case
					When pay_status=0 Then '待报价'
			        When pay_status=1 Then '待付款'
			        When pay_status=2 Then '已付款'
				End 'pay_status'
			From 
				out_firm As ofn
			Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
			    		o_number,
						pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
			    			o_number,
							pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
			    				o_number,
								pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
			    					o_number,
			    					pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id Where wk.state=?) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
			On ofn.oid =ofnrwumw.o_id Order By wid Desc;
		`
		if err = db.Select(&dats, sqlStr, statId); err != nil {
			return nil, errors.New(fmt.Sprintf("获取数据错误，错误代码：%v", err))
		}
	case "1": // 获取 state 状态为 1 的数据
		sqlStr = `
			Select 
				wid,
				state,
				Case 
					When state=0 Then '待收货' 
					When state=1 Then '待派单' 
					When state=2 Then '内部维修中' 
					When state=3 Then '外发维修中' 
					When state=4 Then '待返客户' 
					When state=5 Then '已完成'
				END 'work_state',
				mname,
				model,
				user_name,
				client_name,
			    log_num,
				out_firm_name,
				issue_describe,
				mater_num,
				create_date,
				crate_date,
				express_num,
			    work_type,
			    o_number,
			    Case
					When pay_status=0 Then '待报价'
			        When pay_status=1 Then '待付款'
			        When pay_status=2 Then '已付款'
				End 'pay_status'
			From 
				out_firm As ofn
			Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
			    		o_number,
			    		pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
			    			o_number,
			    			pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
			    				o_number,
			    				pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
			    					o_number,
			    					pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id Where wk.state=?) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
			On ofn.oid =ofnrwumw.o_id Order By wid Desc;
		`
		if err = db.Select(&dats, sqlStr, statId); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据错误，错误代码：%v", err))
		}
	case "2": // 获取 state 状态为 2 或者为 3 的数据
		sqlStr = `
			Select 
				wid,
				state,
				Case 
					When state=0 Then '待收货' 
					When state=1 Then '待派单' 
					When state=2 Then '内部维修中' 
					When state=3 Then '外发维修中' 
					When state=4 Then '待返客户' 
					When state=5 Then '已完成'
				END 'work_state',
				mname,
				model,
				user_name,
				client_name,
			   log_num,
				out_firm_name,
				issue_describe,
				mater_num,
				create_date,
				crate_date,
				express_num,
			   	work_type,
			    o_number,
			    Case
					When pay_status=0 Then '待报价'
			        When pay_status=1 Then '待付款'
			        When pay_status=2 Then '已付款'
				End 'pay_status'
			From 
				out_firm As ofn
			Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
			    		o_number,
			    		pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
			    			o_number,
			    			pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
			    				o_number,
			    				pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
			    					o_number,
			    					pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id Where wk.state=? Or wk.state=?) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
			On ofn.oid =ofnrwumw.o_id Order By wid Desc;
		`
		if err = db.Select(&dats, sqlStr, statId, PROCESS); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据错误，错误代码：%v", err))
		}
	case "4": // 获取 state 状态为 4 的数据
		sqlStr = `
			Select 
				wid,
				state,
				Case 
					When state=0 Then '待收货' 
					When state=1 Then '待派单' 
					When state=2 Then '内部维修中' 
					When state=3 Then '外发维修中' 
					When state=4 Then '待返客户' 
					When state=5 Then '已完成'
				END 'work_state',
				mname,
				model,
				user_name,
				client_name,
			   log_num,
				out_firm_name,
				issue_describe,
				mater_num,
				create_date,
				crate_date,
				express_num,
			    work_type,
			    o_number,
			    Case
					When pay_status=0 Then '待报价'
			        When pay_status=1 Then '待付款'
			        When pay_status=2 Then '已付款'
				End 'pay_status'
			From 
				out_firm As ofn
			Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
			    		o_number,
			    		pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
			    			o_number,
			    			pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
			    				o_number,
			    				pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
			    					o_number,
			    					pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id Where wk.state=?) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
			On ofn.oid =ofnrwumw.o_id Order By wid Desc;
		`
		if err = db.Select(&dats, sqlStr, statId); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据错误，错误代码：%v", err))
		}
	default:
		sqlStr = `
			Select 
				wid,
				state,
				Case 
					When state=0 Then '待收货' 
					When state=1 Then '待派单' 
					When state=2 Then '内部维修中' 
					When state=3 Then '外发维修中' 
					When state=4 Then '待返客户' 
					When state=5 Then '已完成'
				END 'work_state',
				mname,
				model,
				user_name,
				client_name,
			    log_num,
				out_firm_name,
				issue_describe,
				mater_num,
				create_date,
				crate_date,
				express_num,
			    work_type,
			    o_number,
			    Case
					When pay_status=0 Then '待报价'
			        When pay_status=1 Then '待付款'
			        When pay_status=2 Then '已付款'
				End 'pay_status'
			From 
				out_firm As ofn
			Right Join
				(Select 
					wid,
					state,
					mname,
					model,
					user_name,
					client_name,
					log_num,
					o_id,
					issue_describe,
					mater_num,
					create_date,
					crate_date,
					express_num,
					work_type,
					o_number,
					pay_status
				From 
					return_work As rw
				Right Join
					(Select
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,	
						o_id,
						issue_describe,
						mater_num,
						create_date,
						work_type,
			    		o_number,
			    		pay_status
					From
						users As u
					Inner Join
						(Select 
							wid,
							state,
							mname,
							model,
							mw.u_id,
							client_name,
							log_num,
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
							o_number,
							pay_status
						From 
							client As c
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								u_id,
								c_id,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
			    				o_number,
			    				pay_status
							From
								mater As m
							Inner Join
								work As wk
							On m.mid = wk.m_id) As mw
						On c.cid = mw.c_id) As umw
					On u.uid = umw.u_id) As rwumw
				On rw.work_id=rwumw.wid) As ofnrwumw
			On ofn.oid =ofnrwumw.o_id Order By wid Desc;
		`
		if err = db.Select(&dats, sqlStr); err != nil {
			return nil, errors.New(fmt.Sprintf("获取工单数据错误，错误代码：%v", err))
		}
	}
	return
}

// GetOutFirm 获取外发公司数据
func GetOutFirm(oid string) (outs []*OutFirm, err error) {
	switch oid {
	case "":
		sqlStr := `select oid,out_firm_name from out_firm;`
		if err = db.Select(&outs, sqlStr); err != nil {
			return nil, errors.New(fmt.Sprintf("获取对外公司数据失败，错误代码：%v", err))
		}
		return
	default:
		sqlStr := `select oid,out_firm_name,people,phone,oaddr from out_firm Where oid=?;`
		if err = db.Select(&outs, sqlStr, oid); err != nil {
			return nil, errors.New(fmt.Sprintf("获取对外公司数据失败，错误代码：%v", err))
		}
	}
	return
}

// GetWxUsers 获取所有维修人员数据
func GetWxUsers(tid string) (users []*Users, err error) {
	sqlStr := `select uid,user_name from users Where t_id=?;`
	err = db.Select(&users, sqlStr, tid)
	if err != nil {
		return nil, errors.New(fmt.Sprintf("获取所有用户数据操作错误，错误代码：%v", err))
	}
	return
}

// UpdateWorkState 更新工单状态操作
func UpdateWorkState(editType, wid, uid, oid, oNum string) error {
	/*
		editType: 1-表示接收操作, 将 state 更改为 1
		editType: 2-表示派单操作，内派单直接将 state 修改为 2
		editType: 3-表示派单操作，如果是外发单修改 state 为 3，并将外发公司的Id更新到 o_id 字段
		editType: 4-表示退仓操作, 将 state 更改为 4
		editType: 5-表示发货操作, 将 state 更改为 5, 并且将返回信息插入到 return_work 表中
	*/
	sqlStr := `Update work Set state=?,pay_status=? Where wid=?;`
	switch editType {
	case "1":
		if _, err := db.Exec(sqlStr, editType, MtID1, wid); err != nil {
			return errors.New(fmt.Sprintf("接收操作失败，错误代码：%v", err))
		}
	case "2":
		sqlStr = `Update work Set state=?,wu_id=? Where wid=?;`
		if _, err := db.Exec(sqlStr, editType, uid, wid); err != nil {
			return errors.New(fmt.Sprintf("派单操作失败，错误代码：%v", err))
		}
	case "3":
		sqlStr = `Update work Set state=?,o_id=?,o_number=? Where wid=?;`
		if _, err := db.Exec(sqlStr, editType, oid, wid, oNum); err != nil {
			return errors.New(fmt.Sprintf("派单操作失败，错误代码：%v", err))
		}
	case "4":
		if _, err := db.Exec(sqlStr, editType, wid); err != nil {
			return errors.New(fmt.Sprintf("退仓操作失败，错误代码：%v", err))
		}
	case "5":
		if _, err := db.Exec(sqlStr, editType, wid); err != nil {
			return errors.New(fmt.Sprintf("发货操作失败，错误代码：%v", err))
		}
	default:
		return errors.New("非法操作~！")
	}
	return nil
}

// InertDatToRetWork 插入数据到返回信息表中
func InertDatToRetWork(dats *ReturnWork) error {
	sqlStr := `Insert Into return_work(express_id,express_num,crate_date,work_id,ret_num) Values(?,?,?,?,?);`
	if _, err := db.Exec(sqlStr, dats.ExpressId, dats.ExpressNum, dats.CrateDate, dats.WorkId, dats.RetNum); err != nil {
		return errors.New(fmt.Sprintf("发货操作失败，错误代码：%v", err))
	}
	return nil
}

// GetIdMaterInfo 根据物料 ID 获取物料信息
func GetIdMaterInfo(mid string) (mater []*Mater, err error) {
	sqlStr := `Select mid,mname,brand,model From mater Where mid=?;`
	if err = db.Select(&mater, sqlStr, mid); err != nil {
		return nil, errors.New(fmt.Sprintf("根据条码获取设备信息失败，错误代码：%v", err))
	}
	return
}

// GetSonDosage 绑定条码时根据条码获取子件用量
func GetSonDosage(gongwei, code string) (sonAge []*SonDosage, err error) {
	switch gongwei {
	case "0": // 根据半成品条码获取子件用量
		sqlStr := `
		Select 
			mater_type_name,dosage
		From 
			mater_type As mt
		Inner Join
			(Select 
				mname,brand,model,mater_type_id,dosage 
			From 
				mater As m
			Inner Join 
				(Select 
					Distinct mb2.m_id,mb2.dosage From mater_bom As mb2 
				Inner Join  
					(Select 
						Distinct fm_id,mb.m_id
					From 
						mater_bom As mb 
					Inner Join  
						(Select 
							m_id,bom_name_id 
						from 
							code 
						Where 
							barcode = ?
						) As c On mb.m_id = c.m_id
					) As mbc
				On mb2.fm_id = mbc.fm_id) As tmp
			On m.mid = tmp.m_id) As tm
		On mt.mt_id = tm.mater_type_id;
	`
		if err = db.Select(&sonAge, sqlStr, code); err != nil {
			return nil, errors.New(fmt.Sprintf("获取用量失败，错误代码:%v", err))
		}
	case "1": // 根据成品条码获取子件用量
		sqlStr := `
			Select 
				Distinct mater_type_name,mname,dosage
			From 
				mater_type As mt
			Inner Join
				(Select 
					mname,brand,model,mater_type_id,dosage 
				From
					mater As m 
				Inner Join
					(Select 
						mb2.mbid, mb2.m_id,fm_id,dosage
					From 
						mater_bom As mb2
					Inner Join
						(Select 
							mbid,mb.m_id 
						From 
							mater_bom As mb 
						Inner Join 
							(Select 
									m_id,bom_name_id 
								from 
									code 
								Where 
									barcode = ?
								) As c
						On mb.m_id = c.m_id) As mbc
					On mb2.fm_id = mbc.mbid) As tmp
				On m.mid = tmp.m_id) As tmp1
			On mt.mt_id = tmp1.mater_type_id;
		`
		if err = db.Select(&sonAge, sqlStr, code); err != nil {
			return nil, errors.New(fmt.Sprintf("获取用量失败，错误代码:%v", err))
		}
	case "2":
		sqlStr := `
			Select 
				Distinct mater_type_name,dosage,mname
			From 
				mater_type As mt
			Inner Join
				(Select 
					mname,brand,model,mater_type_id,dosage 
				From
					mater As m 
				Inner Join
					(Select 
						mb2.mbid, mb2.m_id,fm_id,dosage
					From 
						mater_bom As mb2
					Inner Join
						(Select 
							mbid,mb.m_id 
						From 
							mater_bom As mb 
						Inner Join 
							(Select 
									m_id,bom_name_id 
								from 
									code 
								Where 
									barcode = ?
								) As c
						On mb.m_id = c.m_id) As mbc
					On mb2.fm_id = mbc.mbid) As tmp
				On m.mid = tmp.m_id) As tmp1
			On mt.mt_id = tmp1.mater_type_id;
		`
		if err = db.Select(&sonAge, sqlStr, code); err != nil {
			return nil, errors.New(fmt.Sprintf("获取用量失败，错误代码:%v", err))
		}
	}
	return
}

// GetCodeId 根据条码获取条码Id
func GetCodeId(code string) (codeId int, err error) {
	sqlStr := `select bid from code Where barcode = ?;`
	if err = db.Get(&codeId, sqlStr, code); err != nil {
		return 0, errors.New(fmt.Sprintf("操作失败，错误代码：%v", err))
	}
	return
}

// InsertCodeIdToCodeBom 插入条码绑定数据到条码 bom 表
func InsertCodeIdToCodeBom(codeBom *CodeBom) (err error) {
	sqlStr := `Insert Into code_bom(b_id,par_id) values(?,?);`
	if _, err = db.Exec(sqlStr, codeBom.BId, codeBom.ParId); err != nil {
		return errors.New(fmt.Sprintf("操作失败，错误代码：%v", err))
	}
	return nil
}

// GetCodeCheckCfg 根据条码查询相关配置信息
func GetCodeCheckCfg(code string) (cfgInfo []*CodeCheckTmpTable, err error) {
	sqlStr := `
		Select 
			Distinct mname,mid,brand,model,u_id,c_id,bom_name_id,barcode,outlib_date
		From 
			mater As m 
		Inner Join
			(Select 
				Distinct bid,m_id,u_id,c_id,bom_name_id,barcode,outlib_date
			From 
				code As c1
			Inner Join
				(Select 
					Distinct cb2.b_id,cb2.par_id
				From 
					code_bom As cb2 
				Inner Join
					(Select 
						b_id,par_id 
					From 
						code_bom As cb 
					Inner Join
						(Select 
							bid
						From 
							code 
						Where 
							barcode = ?) As c
					On cb.b_id = c.bid) As cbc
				On cb2.par_id  = cbc.par_id And cb2.par_id <> ?) As t1
			On c1.bid = t1.b_id) As t2
		On m.mid = t2.m_id;
	`
	if err = db.Select(&cfgInfo, sqlStr, code, MtID0); err != nil {
		return nil, errors.New(fmt.Sprintf("查询失败，错误代码：%v", err))
	}
	return
}

// GetCodeMaterInfo 根据条码获取单个物料信息
func GetCodeMaterInfo(code string) (cfgInfo []*CodeCheckTmpTable, err error) {
	sqlStr := `
		Select 
			Distinct mname,mid,brand,model,u_id,c_id,bom_name_id,barcode,outlib_date
		From 
			mater As m 
			Inner Join(
					Select 
						m_id,u_id,c_id,bom_name_id,barcode,outlib_date
					From 
						code 
					Where 
						barcode = ?) As t1
		On m.mid = t1.m_id;
	`
	if err = db.Select(&cfgInfo, sqlStr, code); err != nil {
		return nil, errors.New(fmt.Sprintf("查询失败，错误代码：%v", err))
	}
	return
}

// GetMaterId 根据条码获取物料Id
func GetMaterId(code string) (mid int, err error) {
	sqlStr := `Select m_id From code Where barcode=?;`
	if err = db.Get(&mid, sqlStr, code); err != nil {
		return 0, errors.New(fmt.Sprintf("错误，错误代码：%v", err))
	}
	return
}

// ClientGetWorkDat 客户手机端获取工单信息
func ClientGetWorkDat(getType string, cid int) (dat []*ClientGetWorkDatTmp, err error) {
	sqlStr := ""
	if getType == "维修工单" {
		sqlStr = `
			Select 
				pay_id,mid,mname,work_type,create_date,log_num,amount,
				CASE 
					When pay_stat=0 Then '待付款'
					When pay_stat=1 Then '已付款'
				END 'pay_stat',
				Case 
					When state=0 Then '待收货'
					When state=1 Then '已收货'
					When state=2 Then '维修中'
					When state=3 Then '维修中'
					When state=4 Then '返回中'
					When state=5 Then '已完成'
				END 'work_state'
			From 
				mater As m
			Inner Join(
				Select 
					pay_id,m_id,work_type,create_date,log_num,amount,pay_stat,state
				From 
					pay As p 
				Inner Join (
					Select 
						m_id,work_type,create_date,log_num,state
					From 
						work
					Where 
						work_type = ? And c_id=?) As w
				On p.mater_id = w.m_id) As pw
			On m.mid  = pw.m_id;
		`
	} else if getType == "返修工单" {
		sqlStr = `
        Select 
			mid,mname,work_type,create_date,log_num,
			Case 
				When state=0 Then '待收货'
				When state=1 Then '已收货'
				When state=2 Then '维修中'
				When state=3 Then '维修中'
				When state=4 Then '返回中'
				When state=5 Then '已完成'
			END 'work_state'
		From 
			mater As m
		Inner Join(
			Select 
					m_id,work_type,create_date,log_num,state
				From 
					work
				Where 
					work_type=? And c_id=?) As pw
		On m.mid  = pw.m_id;
        `
	} else {
		sqlStr = `
			Select 
				pay_id,mid,mname,work_type,create_date,log_num,amount,
				CASE 
					When pay_stat=0 Then '待付款'
					When pay_stat=1 Then '已付款'
				END 'pay_stat',
				Case 
					When state=0 Then '待收货'
					When state=1 Then '已收货'
					When state=2 Then '维修中'
					When state=3 Then '维修中'
					When state=4 Then '返回中'
					When state=5 Then '已完成'
				END 'work_state'
			From 
				mater As m
			Inner Join(
				Select 
					pay_id,m_id,work_type,create_date,log_num,amount,pay_stat,state
				From 
					pay As p 
				Inner Join (
					Select 
						m_id,work_type,create_date,log_num,state
					From 
						work
					Where 
						log_num=? And c_id=?) As w
				On p.mater_id = w.m_id) As pw
			On m.mid  = pw.m_id;
		`
	}
	if err = db.Select(&dat, sqlStr, getType, cid); err != nil {
		return nil, errors.New(fmt.Sprintf("获取工单信息失败，错误代码：%v", err))
	}
	return
}

// GetWorkLinkedId 根据工单Id 获取关联的销售、客户、物料Id
func GetWorkLinkedId(wid string) (info []*Work, err error) {
	sqlStr := `Select u_id,c_id,m_id From work Where wid=?;`
	if err = db.Select(&info, sqlStr, wid); err != nil {
		return nil, errors.New(fmt.Sprintf("接收失败，错误代码：%v", err))
	}
	return
}

// InsertDtToPay 插入数据到支付信息表
func InsertDtToPay(model *Model) (err error) {
	sqlStr := `Insert Into pay(amount,work_id,sale_id,client_id,mater_id) Values(?,?,?,?,?);`
	if _, err = db.Exec(sqlStr, model.Pay.Amount, model.Pay.WorkId, model.Pay.SaleId, model.Pay.ClientId, model.Pay.MaterId); err != nil {
		return errors.New(fmt.Sprintf("接收时创建费用信息失败，错误代码：%v", err))
	}
	return nil
}

// UpdatePayOrderState 更新 pay 表中的订单状态
func UpdatePayOrderState(pid, amount string) (err error) {
	sqlStr := `Update pay Set pay_stat=? Where pay_id=? And amount=?;`
	if _, err = db.Exec(sqlStr, MtID1, pid, amount); err != nil {
		return errors.New(fmt.Sprintf("订单已支付成功，后台修改数据库中的订单状态时出错，您可联系我司售后并将该情况反馈给我司售后部门为您继续提供维修服务，给您带来的不便敬请谅解，谢谢!"))
	}
	return nil
}

// UpdateWorkStatus 支付成功后更新工单状态为已付款
func UpdateWorkStatus(pid string) (err error) {
	sqlStr := `Update work Set pay_status=? Where wid=(Select work_id From pay Where pay_id=?);`
	if _, err = db.Exec(sqlStr, MtID2, pid); err != nil {
		return errors.New(fmt.Sprintf("订单已支付成功，后台修改数据库中的工单状态时出错，您可联系我司售后并将该情况反馈给我司售后部门为您继续提供维修服务，给您带来的不便敬请谅解，谢谢!"))
	}
	return nil
}

// GetUserType 根据用户Id 获取用户类别
func GetUserType(uid int) (tid int, err error) {
	sqlStr := `Select t_id From users Where uid=?;`
	if err = db.Get(&tid, sqlStr, uid); err != nil {
		return 0, errors.New(fmt.Sprintf("获取用户类型失败，错误代码：%v", err))
	}
	return
}

// GetWarrantDate 根据条码获取过期时间
func GetWarrantDate(code string) (dats []*Code, err error) {
	sqlStr := `Select outlib_user_id,outlib_date,warranty_time,link_order From code Where barcode=?;`
	if err = db.Select(&dats, sqlStr, code); err != nil {
		return nil, errors.New(fmt.Sprintf("登录失败，错误代码：%v", err))
	}
	return
}

// GetCodeComBar 通过整机条码获取所有子条码数据
func GetCodeComBar(code string) (aryDat []string, err error) {
	sqlStr := `
		Select 
			barcode 
		From 
			code As t1
		Inner Join(
			Select 
				b_id 
			From 
				code_bom As cb
			Inner Join(
				Select 
					bid 
				From 
					code 
				Where 
					barcode = ?) As c
			On 
				cb.par_id = c.bid) As cbc
		On 
			t1.bid = cbc.b_id;
	`
	if err = db.Select(&aryDat, sqlStr, code); err != nil {
		return nil, errors.New(fmt.Sprintf("根据主条码获取子条码数据错误，错误代码：%v", err))
	}
	return
}

// InsertFileDatToSql 将导入的数据插入到数据库
func InsertFileDatToSql(model *Model) (err error) {
	sqlStr := `Insert Into mater(mname,brand,model,mater_type_id) values(?,?,?,?);`
	if _, err = db.Exec(sqlStr, model.Mater.Mname, model.Mater.Brand, model.Mater.Model, model.Mater.MTypeId); err != nil {
		return errors.New(fmt.Sprintf("导入数据失败,数据存入数据库失败,指定的物料类型Id可能不存在，错误代码：%v", err))
	}
	return nil
}

// GetSearchMaterInfo 根据条件搜索物料数据方法
func GetSearchMaterInfo(mater string) (maters []*Mater, err error) {
	sqlStr := `
		Select 
			mid,mname,brand,model,mater_type_name 
		From 
			mater 
		Inner Join 
			mater_type as mt 
		on 
			mater.mater_type_id = mt.mt_id 
		Where 
			mname=? Or mname Like ?
		Or 
			brand = ? Or brand Like ?
		Or 
			model=? Or model Like ?
		Or 
		    mater_type_id In (Select mt_id From mater_type Where mater_type_name=? Or mater_type_name Like ?);
	`
	if err = db.Select(&maters, sqlStr, mater, "%"+mater+"%", mater, "%"+mater+"%", mater, "%"+mater+"%", mater, "%"+mater+"%"); err != nil {
		return nil, errors.New(fmt.Sprintf("搜索物料数据库读取失败，错误代码：%v", err))
	}
	return
}

// GetSearchWorkInfo 根据条件搜索工单信息
func GetSearchWorkInfo(tpe int, startDate, endStart, iptDat string) (dats []*BusWorkOrderTmp, err error) {
	var sqlStr string
	switch tpe {
	case 0: // 按时间和搜索内容同时过滤
		sqlStr = `
				Select 
					wid,
					state,
					Case 
						When state=0 Then '待收货' 
						When state=1 Then '待派单' 
						When state=2 Then '内部维修中' 
						When state=3 Then '外发维修中' 
						When state=4 Then '待返客户' 
						When state=5 Then '已完成'
					END 'work_state',
					mname,
					model,
					user_name,
					client_name,
					log_num,
					out_firm_name,
					issue_describe,
					mater_num,
					create_date,
					crate_date,
					express_num,
					work_type,
					o_number,
					Case
						When pay_status=0 Then '待报价'
						When pay_status=1 Then '待付款'
						When pay_status=2 Then '已付款'
					End 'pay_status'
				From 
					out_firm As ofn
				Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
						o_number,
						pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,	
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
							o_number,
							pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
								o_number,
								pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
									o_number,
									pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id Where wk.create_date Between ? And ?) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
				On ofn.oid =ofnrwumw.o_id  Where 
					ofnrwumw.mname = ? Or ofnrwumw.mname Like ? 
					Or ofnrwumw.issue_describe = ? Or ofnrwumw.issue_describe Like ? 
					Or ofnrwumw.log_num = ? Or ofnrwumw.log_num Like ? 
					Or ofnrwumw.client_name = ? Or ofnrwumw.client_name Like ? 
				Order By wid Desc;
			`
		if err = db.Select(&dats, sqlStr, startDate, endStart, iptDat, "%"+iptDat+"%", iptDat, "%"+iptDat+"%", iptDat, "%"+iptDat+"%", iptDat, "%"+iptDat+"%"); err != nil {
			return nil, errors.New(fmt.Sprintf("failed to db.Select:%v", err))
		}
	case 1: // 按时间进行过滤搜索
		sqlStr = `
				Select 
					wid,
					state,
					Case 
						When state=0 Then '待收货' 
						When state=1 Then '待派单' 
						When state=2 Then '内部维修中' 
						When state=3 Then '外发维修中' 
						When state=4 Then '待返客户' 
						When state=5 Then '已完成'
					END 'work_state',
					mname,
					model,
					user_name,
					client_name,
					log_num,
					out_firm_name,
					issue_describe,
					mater_num,
					create_date,
					crate_date,
					express_num,
					work_type,
					o_number,
					Case
						When pay_status=0 Then '待报价'
						When pay_status=1 Then '待付款'
						When pay_status=2 Then '已付款'
					End 'pay_status'
				From 
					out_firm As ofn
				Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
						o_number,
						pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,	
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
							o_number,
							pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
								o_number,
								pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
									o_number,
									pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id Where create_date Between ? And ?) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
				On ofn.oid =ofnrwumw.o_id Order By wid Desc;
			`
		if err = db.Select(&dats, sqlStr, startDate, endStart); err != nil {
			return nil, errors.New(fmt.Sprintf("failed to db.Select:%v", err))
		}
	case 2: // 按搜索内容进行搜索
		sqlStr = `
				Select 
					wid,
					state,
					Case 
						When state=0 Then '待收货' 
						When state=1 Then '待派单' 
						When state=2 Then '内部维修中' 
						When state=3 Then '外发维修中' 
						When state=4 Then '待返客户' 
						When state=5 Then '已完成'
					END 'work_state',
					mname,
					model,
					user_name,
					client_name,
					log_num,
					out_firm_name,
					issue_describe,
					mater_num,
					create_date,
					crate_date,
					express_num,
					work_type,
					o_number,
					Case
						When pay_status=0 Then '待报价'
						When pay_status=1 Then '待付款'
						When pay_status=2 Then '已付款'
					End 'pay_status'
				From 
					out_firm As ofn
				Right Join
					(Select 
						wid,
						state,
						mname,
						model,
						user_name,
						client_name,
						log_num,
						o_id,
						issue_describe,
						mater_num,
						create_date,
						crate_date,
						express_num,
						work_type,
						o_number,
						pay_status
					From 
						return_work As rw
					Right Join
						(Select
							wid,
							state,
							mname,
							model,
							user_name,
							client_name,
							log_num,	
							o_id,
							issue_describe,
							mater_num,
							create_date,
							work_type,
							o_number,
							pay_status
						From
							users As u
						Inner Join
							(Select 
								wid,
								state,
								mname,
								model,
								mw.u_id,
								client_name,
								log_num,
								o_id,
								issue_describe,
								mater_num,
								create_date,
								work_type,
								o_number,
								pay_status
							From 
								client As c
							Inner Join
								(Select 
									wid,
									state,
									mname,
									model,
									u_id,
									c_id,
									log_num,
									o_id,
									issue_describe,
									mater_num,
									create_date,
									work_type,
									o_number,
									pay_status
								From
									mater As m
								Inner Join
									work As wk
								On m.mid = wk.m_id) As mw
							On c.cid = mw.c_id) As umw
						On u.uid = umw.u_id) As rwumw
					On rw.work_id=rwumw.wid) As ofnrwumw
				On ofn.oid =ofnrwumw.o_id  Where 
					ofnrwumw.mname = ? Or ofnrwumw.mname Like ? 
					Or ofnrwumw.issue_describe = ? Or ofnrwumw.issue_describe Like ? 
					Or ofnrwumw.log_num = ? Or ofnrwumw.log_num Like ? 
					Or ofnrwumw.client_name = ? Or ofnrwumw.client_name Like ? 
				Order By wid Desc;
			`
		if err = db.Select(&dats, sqlStr, iptDat, "%"+iptDat+"%", iptDat, "%"+iptDat+"%", iptDat, "%"+iptDat+"%", iptDat, "%"+iptDat+"%"); err != nil {
			return nil, errors.New(fmt.Sprintf("failed to db.Select:%v", err))
		}
	default:
		return nil, errors.New(fmt.Sprintf("failed to db.Select：非法操作"))
	}
	return
}

// GetWarehouseDat 根据搜索条件查询出货记录
func GetWarehouseDat(tpe int, startDate, endStart, iptDat string) (dats []*WarehouseDatTmp, err error) {
	sqlStr := ``
	switch tpe {
	case 0: // 查询包含时间段和搜索内容的信息
		sqlStr = `
			Select 
				mid,
				mname,
				brand,
				model,
				barcode,
				outlib_date,
				Case
					When warranty_time=365 Then '一年'
					When warranty_time=180 Then '六个月'
					When warranty_time=30 Then '一个月'
				END 'warranty_time',
				link_order
			From 
				mater As m
			Inner Join
				code As c
			On c.m_id=m.mid Where c.outlib_date Between ? And ? And link_order <> ? And c.link_order=? Order By outlib_date Desc;
		`
		if err = db.Select(&dats, sqlStr, startDate, endStart, "", iptDat); err != nil {
			return nil, errors.New(fmt.Sprintf("获取数据失败，错误代码：%v", err))
		}
	case 1: // 查询时间段包含的信息
		sqlStr = `
			Select 
				mid,
				mname,
				brand,
				model,
				barcode,
				outlib_date,
				Case
					When warranty_time=365 Then '一年'
					When warranty_time=180 Then '六个月'
					When warranty_time=30 Then '一个月'
				END 'warranty_time',
				link_order
			From 
				mater As m
			Inner Join
				code As c
			On c.m_id=m.mid Where c.outlib_date Between ? And ? And c.outlib_date <> ? And link_order <> ? Order By outlib_date Desc;
		`
		if err = db.Select(&dats, sqlStr, startDate, endStart, "", iptDat); err != nil {
			return nil, errors.New(fmt.Sprintf("获取数据失败，错误代码：%v", err))
		}
	case 2: // 查询搜索的内容信息
		sqlStr = `
			Select 
				mid,
				mname,
				brand,
				model,
				barcode,
				outlib_date,
				Case
					When warranty_time=365 Then '一年'
					When warranty_time=180 Then '六个月'
					When warranty_time=30 Then '一个月'
				END 'warranty_time',
				link_order
			From 
				mater As m
			Inner Join
				code As c
			On c.m_id=m.mid Where c.link_order=? Order By outlib_date Desc;
		`
		if err = db.Select(&dats, sqlStr, iptDat); err != nil {
			return nil, errors.New(fmt.Sprintf("获取数据失败，错误代码：%v", err))
		}
	}
	return
}

func InsertNreProductUrlToSql(model *Model) (err error) {
	sqlStr := `Insert Into new_product(img_url,jump_url,remark) values(?,?,?);`
	if *model.NewProduct.Remark == "" {
		sqlStr = `Insert Into new_product(img_url,jump_url) values(?,?);`
		if _, err = db.Exec(sqlStr, model.NewProduct.ImgUrl, model.NewProduct.JumpUrl); err != nil {
			return fmt.Errorf("插入数据到数据库失败，错误代码：%v", err)
		}
	}
	if _, err = db.Exec(sqlStr, model.NewProduct.ImgUrl, model.NewProduct.JumpUrl, model.NewProduct.Remark); err != nil {
		return fmt.Errorf("插入数据到数据库失败，错误代码：%v", err)
	}
	return nil
}

// GetAdData 获取轮播广告图片信息
func GetAdData() (dats []*NewProduct, err error) {
	sqlStr := `Select nid,img_url,jump_url,remark From new_product Order By nid Desc Limit ?, ?;`
	if err = db.Select(&dats, sqlStr, MtID0, MtID4); err != nil {
		return nil, fmt.Errorf("获取图片数据失败，错误代码：%v", err)
	}
	return
}
